#!/usr/bin/env python
# -*-coding:UTF-8-*-
import sys, pymysql, traceback
import time
from Log import log, notice, error
import threading

class mysql:
    def __init__(self,
                 host='',
                 user='',
                 passwd='',
                 db='',
                 port=3306,
                 charset='utf8'
                 ):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port
        self.charset = charset
        self.conn = None
        self._conn()

    def _conn(self):
        try:
            self.conn = pymysql.connect(self.host, self.user, self.passwd, self.db)
            log("SQL连接成功")
            return True
        except:
            log("SQL连接失败")
            return False

    def _reConn(self, num=28800, stime=3):  # 重试连接总次数为1天,这里根据实际情况自己设置,如果服务器宕机1天都没发现就......
        _number = 0
        _status = True
        while _status and _number <= num:
            try:
                self.conn.ping()  # cping 校验连接是否异常
                _status = False
            except:
                if self._conn() == True:  # 重新连接,成功退出
                    _status = False
                    break
                _number += 1
                time.sleep(stime)  # 连接不成功,休眠3秒钟,继续循环，知道成功或重试次数结束

    def sql_run(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.cursor.close()
            log("执行完毕")
            return result
        except:
            log("连接错误")

    def query(self, sql=''):
        try:
            self._reConn()
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            # 设置锁
            thread_lock = threading.Lock()
            # 开启锁
            thread_lock.acquire()
            self.cursor.execute("set names utf8")  # utf8 字符集
            status = self.cursor.execute(sql)
            self.conn.commit()
            result = self.cursor.fetchall()
            # 关闭锁
            thread_lock.release()
            self.cursor.close()
            log("执行的语句为:" + str(sql)+ "，结果为:"+ str(result))
            return True, status, result
        except Exception as e:
            print("\033[31mError : "+str(sql)+" SQL语法错误，返回值为:"+ str(e))
            return False, 0, None

    def close(self):
        self.conn.close()


mysql_c = mysql('服务器地址', '用户名', '密码', '表名', 端口)
